var mysql = require('mysql');
var $sql = require('./sqlMapping')
var formatterDate = require('../util/formatterDate')

var pool = mysql.createPool({
    host     : '127.0.0.1',
    user     : 'root',
    password : 'ly1207',
    database : 'demo'
});
// 向前台返回JSON方法的简单封装
var jsonWrite = function (res, ret) {
    if(typeof ret === 'undefined') {
        res.json({
            code:'1',
            msg: '操作失败'
        });
    } else {
        res.json(ret);
    }
};

module.exports = {
    lists: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;
            let page = (param.page - 1)|| 0
            let start = page * param.num;
            let end = parseInt(param.num);
            connection.query($sql.num, function (err, result) {
                result = formatterDate(result);
                const num = result[0]['count(*)'];
                connection.query($sql.queryPage,[start,end], function (err, result) {
                    if(result){
                        result = {
                            totalPage:Math.ceil(num/param.num),
                            code: 200,
                            data:result,
                            msg: '成功'
                        };
                    }

                    // 以json形式，把操作结果返回给前台页面
                    jsonWrite(res, result);

                    // 释放连接
                    connection.release();
                });


            });
        });
    },
    search: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;
            let search = `%${param.text}%`;

            connection.query($sql.queryByTitle, search,function (err, result) {
                result = formatterDate(result);
                if (result) {
                    result = {
                        code: 200,
                        data:result,
                        msg: '成功'
                    };
                }

                // 以json形式，把操作结果返回给前台页面
                jsonWrite(res, result);

                // 释放连接
                connection.release();
            });
        });
    },
    page: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;
            let page = (param.page - 1)|| 0
            let start = page * param.results || 0;
            let end = parseInt(param.results) || 10;
            connection.query($sql.queryPage,[start,end], function (err, result) {
                if (result) {
                    let data = {
                        info:{
                            total:result[0].total || 0,
                            page:param.page || 0,
                            results:param.results,
                            version:"1.0",
                        },
                        results:result,
                    }
                    result = {
                        code: 200,
                        data:data,
                        msg: '成功'
                    };
                }

                // 以json形式，把操作结果返回给前台页面
                jsonWrite(res, result);

                // 释放连接
                connection.release();
            });
        });
    },
    tagList: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;

            connection.query($sql.queryTagAll, function (err, result) {
                result = formatterDate(result);
                if (result) {
                    result = {
                        code: 200,
                        data:result,
                        msg: '成功'
                    };
                }
                // 以json形式，把操作结果返回给前台页面
                jsonWrite(res, result);

                // 释放连接
                connection.release();
            });
        });
    },
    detail: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;
            // 建立连接，向表中插入值
            connection.query($sql.queryById, param.id, function (err, result) {
                result = formatterDate(result);
                if (result) {
                    result = {
                        code: "success",
                        data:result[0],
                        msg: '成功'
                    };
                }

                // 以json形式，把操作结果返回给前台页面
                jsonWrite(res, result);

                // 释放连接
                connection.release();
            });
        });
    }
}